/*
 * Db.java
 * 
 * Database Connector Class
 *
 * @author david
 */
package org.puresilk.android.db;

import org.puresilk.android.util.Util;
import org.puresilk.android.util.Logger;

import android.content.ContentValues;
import android.content.res.Resources;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;

/**
 * Class to represent the Android on-board 
 * SQLite database.  Handles most ops for 
 * db including initialize, upgrade, open,
 * and close.
 * 
 * For queries, loading up with a Context
 * (<i>org.pursilk.db.Context</i>) gives the db
 * a context for CRUD operations.  The db
 * can will work with the context to generate
 * and execute SQL / DDL operations.
 * 
 * Database meta information is stored in the
 * <i>database.xml</i> resource bundle in 
 * the <i>res/values/</i> folder.
 */
public class Db {
	private static final Logger logger = Logger.getInstance("Db");

	private static final String STRING_TYPE = "string";
	private static final String STRING_ARRAY_TYPE = "array";
	private static final String DATABASE_NAME_LABEL = "dbname";
	private static final String TABLE_LISTING_LABEL = "tables";
	
	// data base and query context
    private SQLiteDatabase db;
    private Context context;
    
	/**
	 * Initialize as new db and generate tables according
	 * to the definitions loaded from the <i>database.xml</i>
	 * resource bundle.  
	 */
	public static void initialize(android.content.Context ctx, boolean dropFirst){
		logger.info("Initializing DB");
		
		// get access to meta data
		Resources res = ctx.getResources();
		
		// get tables listing 
		String [] tbls = res.getStringArray(Util.getResourceId(ctx,TABLE_LISTING_LABEL, STRING_ARRAY_TYPE));
		
		// open says a me!
		Db ldb = new Db();
		ldb.open(ctx);
		
		// build each table in list
		for (int i = 0; i<tbls.length; i++) {
			// what's happening...
			logger.debug("Creating table " + tbls[i]);
			
			int resid = Util.getResourceId(ctx,tbls[i], "array");
			logger.debug("Resource ID " + String.valueOf(resid));
					
			// create entity context
			Context dbCtx = new Context(ctx.getResources().getStringArray(resid));
			
			// create context table
			try {
				// lose it if need asked
				if(dropFirst)
					try {
						ldb.db.execSQL(dbCtx.drop());
					}
					catch(Exception ex){
						// Table may not be there so this is ok
					}
				
				// get DDL from context 
				String ddl = dbCtx.create();
				logger.info("Creating table " + tbls[i] + " ... " + ddl);
				
				// and execute it
				ldb.db.execSQL(ddl);
}
			catch(Exception ex){
				logger.error("Error creating table",ex);
			}
		}
		// clean up and done
		ldb.close();
	}

	/**
	 * Go about the task of upgrading the db by 
	 * dropping and replacing tables.
	 * 
	 * @param res
	 */
	public static void upgrade(android.content.Context ctx){
		Db.initialize(ctx, true);
	}
	
	/**
	 * Default Constructor
	 */
	public Db() { }
	
	/**
     * Init w/Context Constructor.
     * 
     * @param initialCtx Context loader so db is ready to go.
     */
    public Db(Context initialCtx){
       context = initialCtx;
    }
    
    /**
     * Retrieve active entity context.
     * @return
     */
    protected Context getContext(){
    	return context;
    }
    
    /**
     * Replace active entity context.
     * 
     * @param oh
     */
    public void setContext(Context ctx){
    	context = ctx;
    }
     
    /**
     * Close DB connection
     */
    public void close()
    {
    	if(db != null)
    		db.close();
    }
        
    /**
     * Open DB for use by just this
     * app.
     * 
     * @param oh
     */
    public void open(android.content.Context ctx) {
  	    try {
		        db = ctx.openOrCreateDatabase(ctx.getString(Util.getResourceId(ctx,DATABASE_NAME_LABEL, STRING_TYPE)), android.content.Context.MODE_PRIVATE, null);		        
		    } catch(SQLiteException ex) {
		        logger.error("Open database exception caught", ex);
		    }
    }
    /*------------------------------------------------------------
       SQL API - Each has a default implementation that uses the
       resident context as a helper to get the clauses straight.  
       Override any query by using a sub-classed Context and
       overriding specific CRUD methods to produce raw SQL for 
       execution (see the supplied implementation for select).
      ------------------------------------------------------------*/
    
    /**
     * Simple select statement returning
     * ALL columns, no filter, no group by,
     * no having, and no order.
     */
    public Cursor selectAll() {
    	return select(null, null, null, null, null);
    }
    
    /**
     * Query runner
     * (w and w/o distinct keyword)
     */
    public Cursor select(String [] cols, ContentValues fltr,String[] grpby, ContentValues hvg, String[] ordby){
    	return select(cols, fltr, grpby, hvg, ordby, false);
    }
    
    public Cursor select(String [] cols, ContentValues fltr,String[] grpby, ContentValues hvg, String[] ordby, boolean distinct){
       	Cursor crsr = null;
       	
       	String sql = context.select (cols, fltr,grpby,hvg,ordby, distinct);
 
    	if (sql == null)
    		crsr = db.query(context.getEntityName(), cols, context.buildClause(fltr, " and "), null, 
    				        grpby == null ? null : Util.join(grpby,","),hvg == null ? null : context.buildClause(hvg," and "), 
    				        ordby == null ? null : Util.join(ordby,","));
    	else
    		crsr = db.rawQuery(sql,null);
    	// return the cursor
    	return crsr;
    }
    
    /**
	 * Format an update for this entity
	 */
	public void update(ContentValues vals, ContentValues fltr) {
		logger.debug(vals.toString());
		
		String sql = context.update(vals, fltr);
		if (sql == null)
			db.update(context.getEntityName(), vals, fltr == null ? "" : context.buildClause(fltr, " and "), new String[0]);
		else
			db.execSQL(sql);
	}

	/**
	 * Format a delete for this entity
	 */
	public void delete(ContentValues fltr){
		String sql = context.delete(fltr);
		if(sql == null)			
			db.delete(context.getEntityName(), fltr==null ? "" : context.buildClause(fltr, " and "), null);
		else
			db.execSQL(sql);
	}

	/**
	 * Format an insert for this entity
	 */
	public void insert(ContentValues vals){
		String sql = context.insert(vals);
		if(sql == null)
			db.insert(context.getEntityName(),null,vals);
		else
			db.execSQL(sql);
	}
}
